package sw6.admin.database;

import java.io.IOException;
import java.util.Date;

import sw6.admin.parser.FirstRunInsertSettings;
import sw6.admin.parser.SettingsParser;
import sw6.lib.exceptions.SettingNotFoundException;
import android.content.ContentValues;
import android.content.Context;
import android.content.pm.ApplicationInfo;
import android.content.pm.PackageManager.NameNotFoundException;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHelper extends SQLiteOpenHelper {
	
	private Context context;
	
	public static final String TBL_BOOLEANS		= "booleans";
	public static final String TBL_DOUBLES		= "doubles";
	public static final String TBL_ENUMS		= "enums";
	public static final String TBL_ENUM_ELEMS	= "elements";
	public static final String TBL_OBJECTS		= "objects";
	public static final String TBL_INTEGERS 	= "integers";
	public static final String TBL_STRINGS 		= "strings";
	public static final String COL_APP_NAME		= "app_name";
	public static final String COL_VAR_NAME		= "var_name";
	public static final String COL_VAR_VALUE	= "var_value";
	public static final String COL_VAR_MIN		= "min";
	public static final String COL_VAR_MAX		= "max";
	public static final String COL_VAR_CLASS 	= "var_canonical_class_name";
	public static final String COL_TIMESTAMP 	= "timestamp";
	public static final String COL_DIRTY 		= "dirty";
	private static final String DB_NAME         = "db";
	private static final String ENABLE_FK_IN_DB = "PRAGMA foreign_keys=ON;";
	
	public DatabaseHelper(Context context) {
		super(context, DB_NAME, null, 1);
		this.context = context;
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		//Enable foreign key support for the new opened database connection
		db.execSQL(ENABLE_FK_IN_DB);
		
		//Create tables
		createTablesIfNotExists(db);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		//Enable foreign key support for the new opened database connection
		db.execSQL(ENABLE_FK_IN_DB);
		
	}
	
	@Override
	public void onOpen(SQLiteDatabase db) {
		//Enable foreign key support for the new opened database connection
		db.execSQL(ENABLE_FK_IN_DB);
	}
	
    private void createTablesIfNotExists(SQLiteDatabase db) {
    	String[] createTableStatements = {
    			//booleans
    			"CREATE TABLE booleans (" +
	    			"app_name  TEXT," +
	    			"var_name  TEXT," +
	    			"var_value BOOLEAN," +
	    			"timestamp BIGINT," +
	    			"dirty     BOOLEAN," +
	    			"CONSTRAINT 'booleans_primary_key' PRIMARY KEY ( app_name, var_name ));",
    			//doubles
    			"CREATE TABLE doubles (" +
	    			"app_name  TEXT," +
	    			"var_name  TEXT," +
	    			"var_value REAL," +
	    		    "min       REAL," +
	    		    "max       REAL," +
	    			"timestamp BIGINT," +
	    			"dirty     BOOLEAN," +
	    			"CONSTRAINT 'doubles_primary_key' PRIMARY KEY ( app_name, var_name )," +
	    			"CONSTRAINT 'min_max_constraint_check' CHECK ( min <= max AND var_value >= min AND var_value <= max ));",
	    		//enums
			    "CREATE TABLE enums ( " + 
		    	    "app_name  TEXT," +
		    	    "var_name  TEXT," +
		    	    "var_value INT," +
		    	    "timestamp BIGINT," +
		    	    "dirty     BOOLEAN," +
		    	    "FOREIGN KEY ( app_name, var_name, var_value ) " +
		    	    "REFERENCES elements ( app_name, var_name, var_value ) " +
		    	    "ON DELETE CASCADE ON UPDATE CASCADE," +
		    	    "UNIQUE ( app_name, var_name )); ",
	    		//enum-elements
    			"CREATE TABLE elements ( " +
				    "app_name  TEXT," +
				    "var_name  TEXT," +
				    "var_value INT," +
				    "CONSTRAINT 'enum_elements_primary_key' PRIMARY KEY ( app_name, var_name, var_value ));",
				//integers
				"CREATE TABLE integers (" +
					"app_name  TEXT," +
					"var_name  TEXT," +
					"var_value INT," +
				    "min       INT," +
				    "max       INT," +
					"timestamp BIGINT," +
	    			"dirty     BOOLEAN," +
					"CONSTRAINT 'integers_primary_key' PRIMARY KEY ( app_name, var_name )," +
					"CONSTRAINT 'min_max_constraint_check' CHECK ( min <= max AND var_value >= min AND var_value <= max ));",
				//strings
				"CREATE TABLE strings (" +
					"app_name  TEXT," +
					"var_name  TEXT," +
					"var_value TEXT," +
				    "min       INT," +
				    "max       INT," +
					"timestamp BIGINT," +
	    			"dirty     BOOLEAN," +
					"CONSTRAINT 'strings_primary_key' PRIMARY KEY ( app_name, var_name )," +
					"CONSTRAINT 'min_max_constraint_check' CHECK ( min <= max AND length( var_value ) >= min AND length( var_value ) <= max ));",
				//objects
    			"CREATE TABLE objects ( " +
				    "app_name                 TEXT," +
				    "var_name                 TEXT," +
				    "var_value                BLOB," +
				    "var_canonical_class_name TEXT," +
				    "timestamp 				  BIGINT," +
	    			"dirty     				  BOOLEAN," +
				"CONSTRAINT 'objects_primary_key' PRIMARY KEY ( app_name, var_name, var_canonical_class_name ));"
    	};
    	
    	for (String createTableStatement : createTableStatements) {
    		db.execSQL(createTableStatement);
		}
    	
    	FirstRunInsertSettings strategy = new FirstRunInsertSettings(db);
    	
    	for (ApplicationInfo appInfo : context.getPackageManager().getInstalledApplications(0)) {
    		try {
        		String packageName = appInfo.packageName;
    			
    			if (packageName != null && packageName.startsWith("sw6.") && SettingsParser.doWeHaveSettingsFile(context, packageName)) {
    				Log.i("sw6.admin.DatabaseHelper", "Adding package: " + appInfo.packageName);
    				SettingsParser.parseAndStoreSettings(context, packageName, strategy);
    			}
    		} catch (NameNotFoundException e) {
    			e.printStackTrace();
    		} catch (IOException e) {
    			e.printStackTrace();
    		}
    	}
    }

    /* ================== GETTERS ================== */
    public Cursor getBoolean(String appName, String varName) {
    	return getSetting(appName, varName, TBL_BOOLEANS);
    }
    
    public Cursor getDouble(String appName, String varName) {
    	return getSetting(appName, varName, TBL_DOUBLES);
    }
    
    public Cursor getEnum(String appName, String varName) {
    	return getSetting(appName, varName, TBL_ENUMS);
    }
    
    public Cursor getInteger(String appName, String varName) {
    	return getSetting(appName, varName, TBL_INTEGERS);
    }
    
    public Cursor getString(String appName, String varName) {
    	return getSetting(appName, varName, TBL_STRINGS);
    }
    
    public Cursor getObject(String appName, String varName, String varCanonicalClassName) {
    	Cursor settingCursor = this.getReadableDatabase().query(
    			TBL_OBJECTS, 					// table to query
    			new String[] { COL_VAR_VALUE }, // value column
    			COL_APP_NAME  + " = ? AND " + 	// where clause
    			COL_VAR_NAME  + " = ? AND " +
    			COL_VAR_CLASS + " = ?",
    			new String[] {appName, varName, varCanonicalClassName}, 
    			null, 							// group by
    			null, 							// having
    			null);							// order by
    	
    	//Check if cursor contains any rows. 
    	// If not, the request for a setting was formed incorrectly.
    	if(settingCursor.moveToFirst() == false) {
    		settingCursor.close();
    	}
    	return settingCursor;
    }
    
    private Cursor getSetting(String appName, String varName, String table) {
    	Cursor settingCursor = this.getReadableDatabase().query(
    			table, 							// table to query
    			new String[] { COL_VAR_VALUE }, // value column
    			COL_APP_NAME + " = ? AND " + 	// where clause
    			COL_VAR_NAME + " = ?", 
    			new String[] {appName, varName}, 
    			null, 							// group by
    			null, 							// having
    			null);							// order by
    	
    	//Check if cursor contains any rows. 
    	// If not, the request for a setting was formed incorrectly.
    	if(settingCursor.moveToFirst() == false) {
    		settingCursor.close();
    	}
    	return settingCursor;
    }
    
    /* ================== UPDATERS ================== */
    public void updateBoolean(String appName, String varName, boolean varValue) {
    	SQLiteDatabase db = this.getWritableDatabase();
    	updateBoolean(db, appName, varName, varValue);
    	db.close();
    }
    
    public static void updateBoolean(SQLiteDatabase db, String appName, String varName, boolean varValue) {
    	ContentValues contentValues = new ContentValues();
    	contentValues.put(COL_VAR_VALUE, varValue);
    	updateSetting(db, contentValues, appName, varName, TBL_BOOLEANS);
    }
    
    public void updateDouble(String appName, String varName, double varValue) {
    	SQLiteDatabase db = this.getWritableDatabase();
    	updateDouble(db, appName, varName, varValue);
    	db.close();
    }
    
    public static void updateDouble(SQLiteDatabase db, String appName, String varName, double varValue) {
    	ContentValues contentValues = new ContentValues();
    	contentValues.put(COL_VAR_VALUE, varValue);
    	updateSetting(db, contentValues, appName, varName, TBL_DOUBLES);
    }
    
    public void updateEnum(String appName, String varName, int varValue) {
    	SQLiteDatabase db = this.getWritableDatabase();
    	updateEnum(db, appName, varName, varValue);
    	db.close();
    }
    
    public static void updateEnum(SQLiteDatabase db, String appName, String varName, int varValue) {
    	ContentValues contentValues = new ContentValues();
    	contentValues.put(COL_VAR_VALUE, varValue);
    	updateSetting(db, contentValues, appName, varName, TBL_ENUMS);
    }
    
    public void updateInteger(String appName, String varName, int varValue) {
    	SQLiteDatabase db = this.getWritableDatabase();
    	updateInteger(db, appName, varName, varValue);
    	db.close();
    }
    
    public static void updateInteger(SQLiteDatabase db, String appName, String varName, int varValue) {
    	ContentValues contentValues = new ContentValues();
    	contentValues.put(COL_VAR_VALUE, varValue);
    	updateSetting(db, contentValues, appName, varName, TBL_INTEGERS);
    }
    
    public void updateString(String appName, String varName, String varValue) {
    	SQLiteDatabase db = this.getWritableDatabase();
    	updateString(db, appName, varName, varValue);
    	db.close();
    }
    
    public static void updateString(SQLiteDatabase db, String appName, String varName, String varValue) {
    	ContentValues contentValues = new ContentValues();
    	contentValues.put(COL_VAR_VALUE, varValue);
    	updateSetting(db, contentValues, appName, varName, TBL_STRINGS);
    }
    
    public void updateObject(String appName, String varName, String varCanonicalClassName, byte[] varValue) {
    	SQLiteDatabase db = this.getWritableDatabase();
    	updateObject(db, appName, varName, varCanonicalClassName, varValue);
    	db.close();
    }
    
    public static void updateObject(SQLiteDatabase db, String appName, String varName, String varCanonicalClassName, byte[] varValue) {
    	ContentValues contentValues = new ContentValues();
    	contentValues.put(COL_VAR_VALUE, varValue);
    	contentValues.put(COL_VAR_CLASS, varCanonicalClassName);
    	contentValues.put(COL_TIMESTAMP, new Date().getTime());
    	contentValues.put(COL_DIRTY, true);
    	int affectedRows = db.update(
    			TBL_OBJECTS, 
    			contentValues, 
    			COL_APP_NAME  + " = ? AND " + 
    			COL_VAR_NAME  + " = ? AND " + 
    			COL_VAR_CLASS + " = ?", 
    			new String[] { appName, varName, varCanonicalClassName });
    	if(affectedRows == 0) {
    		throw new SettingNotFoundException(appName, varName, TBL_OBJECTS, varCanonicalClassName);
    	}
    }
    
    private static void updateSetting(SQLiteDatabase db, ContentValues contentValues, String appName, String varName, String table) {
    	contentValues.put(COL_TIMESTAMP, new Date().getTime());
    	contentValues.put(COL_DIRTY, true);
    	int affectedRows = db.update(
    			table, 
    			contentValues, 
    			COL_APP_NAME + " = ? AND " + 
    			COL_VAR_NAME + " = ?", 
    			new String[] { appName, varName });
    	if(affectedRows == 0) {
    		throw new SettingNotFoundException(varName, appName, table);
    	}    	
    }
    
    /* ================== INSERTERS ================== */
    public void insertBoolean(String appName, String varName, boolean varValue) {
    	SQLiteDatabase db = this.getWritableDatabase();
    	insertBoolean(db, appName, varName, varValue);
    	db.close();
    }
    
    public static void insertBoolean(SQLiteDatabase db, String appName, String varName, boolean varValue) {
    	ContentValues contentValues = new ContentValues();
    	contentValues.put(COL_VAR_VALUE, varValue);
    	insertSetting(db, contentValues, appName, varName, TBL_BOOLEANS);
    }
    
    public void insertDouble(String appName, String varName, double varValue, double min, double max) {
    	SQLiteDatabase db = this.getWritableDatabase();
    	insertDouble(db, appName, varName, varValue, min, max);
    	db.close();
    }
    
    public static void insertDouble(SQLiteDatabase db, String appName, String varName, double varValue, double min, double max) {
    	ContentValues contentValues = new ContentValues();
    	contentValues.put(COL_VAR_VALUE, varValue);
    	contentValues.put(COL_VAR_MIN, min);
    	contentValues.put(COL_VAR_MAX, max);
    	insertSetting(db, contentValues, appName, varName, TBL_DOUBLES);
    }
    
    public void insertEnum(String appName, String varName, int varValue) {
    	SQLiteDatabase db = this.getWritableDatabase();
    	insertEnum(db, appName, varName, varValue);
    	db.close();
    }
    
    public static void insertEnum(SQLiteDatabase db, String appName, String varName, int varValue) {
    	ContentValues contentValues = new ContentValues();
    	contentValues.put(COL_VAR_VALUE, varValue);
    	insertSetting(db, contentValues, appName, varName, TBL_ENUMS);
    }
    
    public void insertEnumElement(String appName, String varName, int varValue) {
    	SQLiteDatabase db = this.getWritableDatabase();
    	insertEnumElement(db, appName, varName, varValue);
    	db.close();
    }
    
    public static void insertEnumElement(SQLiteDatabase db, String appName, String varName, int varValue) {
    	ContentValues contentValues = new ContentValues();
    	contentValues.put(COL_VAR_VALUE, varValue);
    	insertSetting(db, contentValues, appName, varName, TBL_ENUM_ELEMS);
    }
    
    public void insertInteger(String appName, String varName, int varValue, int min, int max) {
    	SQLiteDatabase db = this.getWritableDatabase();
    	insertInteger(db, appName, varName, varValue, min, max);
    	db.close();
    }
    
    public static void insertInteger(SQLiteDatabase db, String appName, String varName, int varValue, int min, int max) {
    	ContentValues contentValues = new ContentValues();
    	contentValues.put(COL_VAR_VALUE, varValue);
    	contentValues.put(COL_VAR_MIN, min);
    	contentValues.put(COL_VAR_MAX, max);
    	insertSetting(db, contentValues, appName, varName, TBL_INTEGERS);
    }
    
    public void insertString(String appName, String varName, String varValue, int min, int max) {
    	SQLiteDatabase db = this.getWritableDatabase();
    	insertString(db, appName, varName, varValue, min, max);
    	db.close();
    }
    
    public static void insertString(SQLiteDatabase db, String appName, String varName, String varValue, int min, int max) {
    	ContentValues contentValues = new ContentValues();
    	contentValues.put(COL_VAR_VALUE, varValue);
    	contentValues.put(COL_VAR_MIN, min);
    	contentValues.put(COL_VAR_MAX, max);
    	insertSetting(db, contentValues, appName, varName, TBL_STRINGS);
    }
    
    public void insertObject(String appName, String varName, String varCanonicalClassName, byte[] varValue) {
    	SQLiteDatabase db = this.getWritableDatabase();
    	insertObject(db, appName, varName, varCanonicalClassName, varValue);
    	db.close();
    }
    
    public static void insertObject(SQLiteDatabase db, String appName, String varName, String varCanonicalClassName, byte[] varValue) {
    	ContentValues contentValues = new ContentValues();
    	contentValues.put(COL_VAR_CLASS, varCanonicalClassName);
    	contentValues.put(COL_VAR_VALUE, varValue);
    	insertSetting(db, contentValues, appName, varName, TBL_OBJECTS);
    }
    
    private static void insertSetting(SQLiteDatabase db, ContentValues contentValues, String appName, String varName, String table) {
    	contentValues.put(COL_APP_NAME, appName);
    	contentValues.put(COL_VAR_NAME, varName);
    	if(!table.equalsIgnoreCase(TBL_ENUM_ELEMS)) {
    		contentValues.put(COL_TIMESTAMP, new Date().getTime());
    		contentValues.put(COL_DIRTY, true);
    	}
    	long newRowId = db.insertOrThrow(table, null, contentValues);
    	
    	/*
    	 * Please note: android.database.sqlite.SQLiteDatabase.insert as used in this method
    	 * does not throw any exception. It catches SQLiteException itself and returns -1 in that case.
    	 * As SQLiteConstraintException extends SQLiteException, this type of exception is catched too, 
    	 * meaning that we cannot identify the exact reason for a failed insert. This is why the error message
    	 * is written to remind the developer, that the insert might have failed due to several reasons.
    	 * The only way to find out exactly why an insert failed, is to look in LogCat while the insert
    	 * is executed at runtime.
    	 */
    	if(newRowId == -1) {
    		throw new SQLiteException("An error occurred when trying to insert the variable named: " + varName + ", " +
    				"for app.: " + appName + ", into table: " + table + ". The setting might already exist or a constraint " +
    						"might have been violated.");
    	}
    }
    
    /* ================== DELETERS ================== */
    public void truncateTable(String... tables) {
    	SQLiteDatabase sqliteDatabase = this.getWritableDatabase();
    	
    	//truncate all tables
    	for(String table : tables) {
    		sqliteDatabase.delete(table, null, null);
    	}
    	
    	sqliteDatabase.close();
    }
    
    public void truncateAllTables() {
    	truncateTable(TBL_BOOLEANS, TBL_DOUBLES, TBL_ENUM_ELEMS, TBL_INTEGERS, TBL_OBJECTS, TBL_STRINGS);
    }
    
    /**
     * Deletes all variables/data belonging to a given application (boolean, integers, doubles, objects and strings)
     * @param appName The name of some application.
     */
    public void deleteApp(String appName) {
    	SQLiteDatabase sqliteDatabase = this.getWritableDatabase();
    	
    	sqliteDatabase.delete(TBL_BOOLEANS, 	COL_APP_NAME + " = ?", new String[] {appName});
    	sqliteDatabase.delete(TBL_DOUBLES, 		COL_APP_NAME + " = ?", new String[] {appName});
    	sqliteDatabase.delete(TBL_ENUM_ELEMS, 	COL_APP_NAME + " = ?", new String[] {appName});
    	sqliteDatabase.delete(TBL_INTEGERS, 	COL_APP_NAME + " = ?", new String[] {appName});
    	sqliteDatabase.delete(TBL_OBJECTS, 		COL_APP_NAME + " = ?", new String[] {appName});
    	sqliteDatabase.delete(TBL_STRINGS, 		COL_APP_NAME + " = ?", new String[] {appName});
    	sqliteDatabase.close();
    }
    
    public static boolean checkDataBaseExsists(Context context) {
        SQLiteDatabase checkDB = null;
        try {
            checkDB = SQLiteDatabase.openDatabase(context.getDatabasePath(DB_NAME).getAbsolutePath(), null, SQLiteDatabase.OPEN_READONLY);
            checkDB.close();
            Log.i("DatabaseHelper", "Database exists!");
        } catch (SQLiteException e) {
            // database doesn't exist yet.
        	Log.i("DatabaseHelper", "Database doesn't exists!");
        }
        return checkDB != null ? true : false;
    }
}
